package com.winning.sx.microframework.dao.sysmanage;

import com.winning.dapi.data.api.IDataAccessService;
import com.winning.dapi.data.entity.DataTable;
//import com.winning.sx.microframework.commonon.exception.CustomException;
//import com.winning.sx.microframework.common.model.SelectModel;
//import com.winning.sx.microframework.model.manage.UserManageModel;
import com.winning.sx.microframework.common.util.SqlTool;
import com.winning.sx.microframework.model.SelectModel;
import com.winning.sx.microframework.model.exception.CustomException;
import com.winning.sx.microframework.model.manage.UserManageModel;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by MJ on 2016-09-26.
 */
@Repository
public class UserManageDao {
    @Autowired
    @Qualifier("dataSource")
    private IDataAccessService dataAccessService;
    final String SYS_VALIDFLAG_TRUE = "100001";
    //@Autowired
    //@Qualifier("dsHIS")
    //private IDataAccessService dasHIS;

    /**
     * 获取用户信息列表
     *
     * @return
     * @throws CustomException
     */
    public DataTable queryUserInfo() throws CustomException {
        DataTable table = null;
        try {
            String sql = "select cUserID,cUserCode,cUserName,bState=CONVERT(INT, bState),cDescribe,createTime from pm_users a order by cUserCode asc";
            table = dataAccessService.querySql(sql);
        } catch (Exception e) {
            throw new CustomException(-2001, "获取数据失败");
        }
        return table;
    }

    /**
     * 获取用户个人信息
     *
     * @return
     * @throws CustomException
     */
    public UserManageModel queryUserInfo(String cUserCode) throws CustomException {
        List<UserManageModel> userManageModels = null;
        UserManageModel userManageModel = null;
        try {
            String sql = "select cUserID,cUserCode,cUserName,cDeptID,bState,cDescribe,createTime from pm_users WHERE cUserCode=?";
            userManageModels = dataAccessService.querySqlForBean(sql, UserManageModel.class, cUserCode);
            userManageModel = userManageModels.get(0);
        } catch (Exception e) {
            throw new CustomException(-2001, "获取数据失败");
        }
        return userManageModel;
    }

    /**
     * 新增用户信息
     *
     * @param map
     * @throws CustomException
     */
    public int newUserInfo(Map map) throws CustomException {
        int result = -1;
        try {
            map.put("validFlag","100001");
            String sql = "insert into pm_users(cUserID,cUserCode,cUserName,cPassword,bState,cDescribe,createTime,validFlag)" +
                    "   values(:cUserID,:cUserCode,:cUserName,:cPassword,:bState,:cDescribe,:createTime,:validFlag)";
            result = dataAccessService.executeSqlUsingParam(sql, map);
        } catch (Exception e) {
            throw new CustomException(-2002, "新增数据失败", e.getMessage(), e.getCause());
        }
        return result;
    }
    public boolean insertUser(String TABLE_NAMNE,Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行insert");
            }
            String sql = "set nocount on;" + "insert into " + TABLE_NAMNE + " ";
            String keys = "", values = "";
            for (String key : param.keySet()) {
                    keys += ("," + key);
            }
            for (String key : param.keySet()) {
                    values += (",'" + param.get(key) + "'");
            }
            sql += ("(" + keys.substring(1) + ",validFlag,createTime) values (" + values.substring(1) + ",'" + SYS_VALIDFLAG_TRUE + "'," + SqlTool.getSysTime() + ");" + "SELECT SCOPE_IDENTITY() as cUserID;");
            dataAccessService.querySql(sql);
            String id =param.get("cUserID").toString();
            param.put("cUserID", id);
            if (id==null) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("插入信息失败！");
        }
    }
    public boolean insertRela(String TABLE_NAMNE,Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行insert");
            }
            String sql = "set nocount on;" + "insert into " + TABLE_NAMNE + " ";
            String keys = "", values = "";
            for (String key : param.keySet()) {
                keys += ("," + key);
            }
            for (String key : param.keySet()) {
                values += (",'" + param.get(key) + "'");
            }
            sql += ("(" + keys.substring(1) + ",validFlag,createTime) values (" + values.substring(1) + ",'" + SYS_VALIDFLAG_TRUE + "'," + SqlTool.getSysTime() + ");" + "SELECT SCOPE_IDENTITY() as cUserID;");
            DataTable dataTable=dataAccessService.querySql(sql);
            if(dataTable.getDataRows().size()>0){
                return true;
            }else{
                return false;
            }
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("插入信息失败！");
        }
    }
    public Map queryEntity(String TABLE_NAMNE,Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where validFlag= '" + SYS_VALIDFLAG_TRUE + "'";
            for (String key : param.keySet()) {
                if (!"".equals(param.get(key)))
                    if (!"".equals(param.get(key)))
                        sql += " and " + key + "='" + param.get(key) + "'";
            }
            table = dataAccessService.querySql(sql);
            if (table.getTotalRowNum() < 1) {
                return null;
            } else if (table.getTotalRowNum() > 1) {
                throw new SQLException("数据库中存在多行被查询数据！");
            } else {
                return table.getDataRows().get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
    }
    /**
     * 修改用户信息
     *
     * @param map
     * @throws CustomException
     */
    public int editUserInfo(Map map) throws CustomException {
        int result = -1;
        try {
            String sql = "update pm_users set cUserName=:cUserName,cUserCode=:cUserCode,cDeptID=:cDeptID,bState=:bState,cDescribe=:cDescribe,createTime=:createTime where cUserID=:cUserID";
            result = dataAccessService.executeSqlUsingParam(sql, map);
        } catch (Exception e) {
            throw new CustomException(-2003, "修改数据失败", e.getMessage(), e.getCause());
        }
        return result;
    }

    /**
     * 修改密码
     *
     * @param map
     * @throws CustomException
     */
    public int updatePassword(Map map) throws CustomException {
        int result = -1;
        try {
            String sql = "update pm_users set cPassword=:cPassword where cUserCode=:cUserCode";
            result = dataAccessService.executeSqlUsingParam(sql, map);
        } catch (Exception e) {
            throw new CustomException(-2003, "修改数据失败", e.getMessage(), e.getCause());
        }
        return result;
    }

    /**
     * 删除用户数据
     *
     * @param map
     * @throws CustomException
     */
    public int deleteUserInfo(Map map) throws CustomException {
        int result = -1;
        String sql;
        try {
            sql = "delete from pm_userasrole WHERE cUserID=:cUserID";
            dataAccessService.executeSqlUsingParam(sql, map);
            sql = "DELETE FROM PM_USERS WHERE cUserID=:cUserID";
            result = dataAccessService.executeSqlUsingParam(sql, map);
        } catch (Exception e) {
            throw new CustomException(-2004, "删除数据失败", e.getMessage(), e.getCause());
        }
        return result;
    }

    public DataTable queryURasso(Map<String, Object> map) throws CustomException {
        DataTable table = null;
        try {
            String sql = "SELECT cRoleID, cRoleName, CASE WHEN cRoleID IN (SELECT cRoleID" +
                    "                                                  FROM PM_UserAsRole" +
                    "                                                  WHERE cUserID=:cUserID) THEN 1" +
                    "                                ELSE 0" +
                    "                           END AS active" +
                    "  FROM PM_ROLES" +
                    "  ORDER BY iOrder";
            table = dataAccessService.querySqlUsingParam(sql, map);
        } catch (Exception e) {
            throw new CustomException(-2001, "获取数据失败");
        }
        return table;
    }

    public int saveAsso(Map<String, Object> map) throws CustomException {
        int result = -1;
//        Map<String, Object> ceMap = new HashMap<String, Object>();
//        ceMap.put("cUserCode", map.get("cUserCode"));
        String sql = "DELETE FROM pm_userasrole WHERE cUserID=:cUserID";
        int row = -1;
        if (map.size() > 0) {
            row = dataAccessService.executeSqlUsingParam(sql, map);
        }
        if (row > -1) {
            result = 0;
//            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            for (Map<String, Object> evnMap : (List<Map<String, Object>>) map.get("selected")) {
                Map<String, Object> seMap = new HashMap<String, Object>();
                seMap.put("cUserID", map.get("cUserID"));
                seMap.put("cRoleID", evnMap.get("cRoleID"));
                String sql1 = "insert into pm_userasrole(cRoleID, cUserID) values(:cRoleID, :cUserID)";
                row = dataAccessService.executeSqlUsingParam(sql1, seMap);
                if (row > 0)
                    result++;
//                list.add(seMap);
            }
//            result=0;
//            for (Map<String, Object> map1 : list) {
//                String sql1 = "insert into pm_userasrole(cRoleID, cUserCode) values(:cRoleID, :cUserCode)";
//                row = dataAccessService.executeSqlUsingParam(sql1, map1);
//                if (row > 0)
//                    result++;
//            }
        }
        return result;
    }

    /**
     * 同步部门字典
     *
     * @throws CustomException
     */
    //public int syncDept() throws CustomException {
    //    int result = -1;
    //    String listsql = "SELECT No_Dept AS cDeptID,DeptCode AS cDeptCode,DeptName AS cDeptName FROM DictDept";
    //    String trsql = "TRUNCATE TABLE PM_Dept";
    //    try {
    //        List<DictDeptModel> list = dasHIS.querySqlForBean(listsql, DictDeptModel.class);
    //        dataAccessService.executeSql(trsql);
    //        int row = 0;
    //        for (DictDeptModel dept : list) {
    //            String sql = "INSERT INTO PM_Dept(cDeptID,cDeptCode,cDeptName) VALUES(?,?,?)";
    //            row = dataAccessService.executeSql(sql, dept.getcDeptID(), dept.getcDeptCode(), dept.getcDeptName());
    //            if (row > 0)
    //                result++;
    //        }
    //    } catch (Exception e) {
    //        throw new CustomException(-2005, "同步数据失败");
    //    }
    //    return result;
    //}

    /**
     * 获取字典列表
     *
     * @return
     */
    public List<SelectModel> getDeptList() {
        String sql = "SELECT cDeptID AS value,cDeptName AS text FROM PM_Dept";
        List<SelectModel> list = null;
        list = dataAccessService.querySqlForBean(sql, SelectModel.class);
        return list;
    }

    public DataTable getNextUserCode() throws CustomException {
        DataTable table = null;
        try {
            table = dataAccessService.querySql("SELECT RIGHT('0000'+CAST(CASE WHEN (MAX(cUserCode) IS NULL) THEN 1 ELSE MAX(cUserCode)+1 END AS nvarchar(50)),4) as nextcode FROM PM_USERS WHERE cUserCode!='9999'");
        } catch (Exception e) {
            throw new CustomException(-2005, "获取主键信息失败！");
        }
        return table;
    }
}
